package S201726702142;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.IntSummaryStatistics;
import java.util.List;
import java.util.stream.IntStream;
import java.util.stream.Stream;

public class App {

	private static Connection conn;
	private static Statement stat;

	public static void main(String[] args) {
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			// 按照课程查询
			sortByCourse();

			// 按照班级查询
			sortByClass();

			// 依照学号与姓名查询
			selectByDefault(1, "id");

			selectByDefault("优优", "name");

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void statistics_people(List<Integer> list) {
		IntSummaryStatistics stastic = list.stream().mapToInt((x) -> x).summaryStatistics();

		System.out.println(
				"课程最高分： " + stastic.getMax() + " 课程最低分： " + stastic.getMin() + " 课程平均分： " + stastic.getAverage());

		int li2 = (int) list.stream().mapToInt(x -> x).filter(x -> {
			return x < 60;
		}).summaryStatistics().getCount();

		System.out.println("低于60分的有：  " + li2);

		int li3 = (int) list.stream().mapToInt(x -> x).filter(x -> {
			return x >= 60 && x < 80;
		}).summaryStatistics().getCount();

		System.out.println("高于60分低于80的有：  " + li3);

		int li4 = (int) list.stream().mapToInt(x -> x).filter(x -> {
			return x >= 80;
		}).summaryStatistics().getCount();
		System.out.println("高于80的有：  " + li4);
	}

	public static void sortByCourse() {
		String sql = "select grade from cs";

		List<Integer> list = new ArrayList<Integer>();
		try {
			ResultSet rs = stat.executeQuery(sql);
			for (; rs.next();) {
				list.add(Integer.valueOf(rs.getInt(1)));
			}
			System.out.println("<----------------CS课程的统计结果：---------------------------------->");
			statistics_people(list);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void sortByClass() {
		String sql = "select distinct classes from cs";

		List<String> list = new ArrayList<String>();

		try {
			ResultSet rs = stat.executeQuery(sql);
			for (; rs.next();) {
				list.add(rs.getString(1));
			}

			list.stream().forEach((x) -> {
				sorted(x);
			});

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void sorted(String str) {
		String sql = "select grade from cs where classes=" + "'" + str + "'";
		try {
			ResultSet rs = stat.executeQuery(sql);

			List<Integer> list = new ArrayList<Integer>();
			for (; rs.next();) {
				list.add(Integer.valueOf(rs.getInt(1)));
			}
			System.out.println(" <-----------" + str + "----------------> 成绩总结：");
			statistics_people(list);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static <T> void selectByDefault(T t, String str) {
		System.out.println("<------------------------" + str + ":" + t.toString() + "  的成绩是：-------------------->");
		String sql = "select grade from cs where " + str + "=" + "'" + t.toString() + "'";
		try {
			ResultSet rs = stat.executeQuery(sql);
			for (; rs.next();) {
				System.out.println(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}
